﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using ICSharpCode.SharpZipLib.Checksums;
using ICSharpCode.SharpZipLib.Zip;
using Yogesh.ExcelXml;
using Lime49;
using System.Windows;
using ResourceBlender.Core;

namespace ResourceBlender.DAL {
    /// <summary>
    /// Generates resources and outputs to various formats
    /// </summary>
    public static class ResourceGenerator {
        /// <summary>
        /// Creates Dictionaries to store bundles from the database. 
        /// Return value format is: A KeyValuePair containing &lt;summary (or null if not needed), Dictionary&lt;bundlename, Dictionary&lt;language-code, Dictionary&lt;element, resource&gt;&gt;&gt;&gt;
        /// </summary>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="threshold">The completion threshold.</param>
        /// <param name="includedBundles">The bundles to include.</param>
        /// <param name="includedLanguages">The language codes to include.</param>
        /// <param name="defaultLanguage">The default language.</param>
        /// <param name="includeSummary"><c>True</c> if a summary of the generated resources should be included.</param>
        /// <returns>A KeyValuePair containing &lt;summary (or null if not needed), Dictionary&lt;bundlename, Dictionary&lt;language-code, Dictionary&lt;element, resource&gt;&gt;&gt;&gt;</returns>
        public static KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> GenerateBundles(string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
            Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = new Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>(); // <BundleName, <abbrev, translatedResources>>>
            List<string> errors = new List<string>();
            StringBuilder summary = null;
            if(includeSummary) {
                summary = new StringBuilder().AppendFormat("Resources generated by ResourceBlender v{0} on {1}\n\n", Constants.GetVersion().ToString(3), DateTime.Now.ToShortDateString());
            }

            Dictionary<string, int> completionMap = new Dictionary<string, int>();
            int totalStrings = 0;
            Dictionary<string, Dictionary<string, RBTranslation>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, RBTranslation>>();
            Database db = new Database();
            db.OpenConnection();
            using(Command cmd = db.CreateCommand()) {
                cmd.CommandText = string.Format(@"SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM {0}elements AS e INNER JOIN {0}resources AS r 
                ON r.resourceid = e.resourceid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang {1}", db.TablePrefix, db.GenerateInClause("bundle", includedBundles, true));
                cmd.AddParameterWithValue("@applicationId", applicationId);
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for the default language
                    string bundleName = reader["bundlename"].ToString(); // id of the bundle
                    string elementName = reader["elementname"].ToString(); // eg: element
                    if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                        defaultLanguageStrings[bundleName] = new Dictionary<string, RBTranslation>();
                    }
                    defaultLanguageStrings[bundleName][elementName] = new RBTranslation(string.Empty, reader["translation"].ToString(), reader["translatorcomment"].ToString());
                    totalStrings++;
                }
                reader.Close();
                if(includeSummary) {
                    summary.AppendFormat("Application '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> bundle in defaultLanguageStrings) {
                        summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                    }
                    summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
                }

                cmd.CommandText = string.Format(@"SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM {0}elements AS e INNER JOIN {0}resources AS r 
                ON r.resourceid = e.resourceid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang {1} {2}",
                    db.TablePrefix, db.GenerateInClause("language", includedLanguages, true), db.GenerateInClause("bundle", includedBundles, true));
                reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                    string abbrev = reader["language"].ToString();
                    string bundle = reader["bundle"].ToString(); // id of the bundle
                    string bundleName = reader["bundlename"].ToString(); // eg: bundle1
                    string elementName = reader["elementname"].ToString(); // eg: element
                    if(includedBundles.Contains(bundle)) {
                        if(!completionMap.ContainsKey(abbrev)) {
                            completionMap[abbrev] = 0;
                        }
                        completionMap[abbrev]++;
                        if(!bundles.ContainsKey(bundleName)) { //  first element in this bundle
                            bundles[bundleName] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                        }
                        if(!bundles[bundleName].ContainsKey(abbrev)) {
                            bundles[bundleName][abbrev] = new Dictionary<string, RBTranslation>();
                        }
                        bundles[bundleName][abbrev][elementName] = new RBTranslation(string.Empty, reader["translation"].ToString(), reader["translatorcomment"].ToString());
                    }
                }
                reader.Close();

                if(includedLanguages.Contains(defaultLanguage)) { // if the user chose to include the default language in the zip
                    if(includeSummary) {
                        string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                        summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                    }
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> defaultBundle in defaultLanguageStrings) { // the default language is loaded separately from the rest, merge them here
                        if(!bundles.ContainsKey(defaultBundle.Key)) { //  first element in this bundle
                            bundles[defaultBundle.Key] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                        }
                        if(!bundles[defaultBundle.Key].ContainsKey(defaultLanguage)) {
                            bundles[defaultBundle.Key][defaultLanguage] = new Dictionary<string, RBTranslation>();
                        }
                        foreach(KeyValuePair<string, RBTranslation> element in defaultBundle.Value) {
                            bundles[defaultBundle.Key][defaultLanguage][element.Key] = new RBTranslation(string.Empty, element.Value.Translation, element.Value.Comment);
                        }
                    }
                }

                // remove languages below the completion threshold
                List<string> incompleteLanguages = new List<string>();
                foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=number of translatedStrings
                    int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                    if(completionPercentage > threshold) {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.Append("*").AppendFormat(ResourceBlender.Core.I18nUtils.GetString("UIStrings", "IncludedLangCompletion", languageName, languageCompletion.Key, completionPercentage.ToString())).Append("\n");
                        }
                        foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> bundle in defaultLanguageStrings) { // key=bundlename, val=Dictionary<element,translation>
                            if(!bundles.ContainsKey(bundle.Key)) {
                                bundles[bundle.Key] = new Dictionary<string, Dictionary<string, RBTranslation>>();
                            }
                            if(!bundles[bundle.Key].ContainsKey(languageCompletion.Key)) {
                                bundles[bundle.Key].Add(languageCompletion.Key, bundle.Value);
                            } else {
                                foreach(KeyValuePair<string, RBTranslation> element in bundle.Value) {
                                    if(!bundles[bundle.Key][languageCompletion.Key].ContainsKey(element.Key)) {
                                        bundles[bundle.Key][languageCompletion.Key].Add(element.Key, element.Value);
                                    }
                                }
                            }
                        }
                    } else {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.Append("*").AppendFormat(ResourceBlender.Core.I18nUtils.GetString("UIStrings", "OmittedLangCompletion", languageName, languageCompletion.Key, completionPercentage.ToString())).Append("\n");
                        }
                        foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> form in bundles) {
                            form.Value.Remove(languageCompletion.Key);
                        }
                    }
                }
                return new KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>>(includeSummary ? summary.ToString() : null, bundles);
            }
        }

        /// <summary>
        /// Outputs to Firefox .properties and .dtd files.
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="threshold">The completion threshold.</param>
        /// <param name="includedBundles">The bundles to include.</param>
        /// <param name="includedLanguages">The languages to include.</param>
        /// <param name="defaultLanguage">The default language.</param>
        /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
        /// <param name="escapeUnicode"><c>true</c> if code points above 127 (7F) should be replaced with their unicode escape sequences (\uXXXX).</param>
        public static void GenerateFirefox(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool escapeUnicode) {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
            List<string> errors = new List<string>();
            StringBuilder summary = null;
            if(includeSummary) {
                summary = new StringBuilder("Resources generated by ResourceBlender v").Append(Constants.GetVersion().ToString(3))
                                    .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
            }

            Dictionary<string, int> completionMap = new Dictionary<string, int>();
            int totalStrings = 0;
            Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>();
            Database db = new Database();
            db.OpenConnection();
            using(Command cmd = db.CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ");
                sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
                cmd.CommandText = sqlBuffer.ToString();
                cmd.AddParameterWithValue("@applicationId", applicationId);
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                    string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                    string elementName = reader["elementname"].ToString(); // eg: element
                    if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this form name
                        defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                    }
                    defaultLanguageStrings[bundleName][elementName] = reader["translation"].ToString();
                    totalStrings++;
                }
                reader.Close();
                if(includeSummary) {
                    summary.AppendFormat("Firefox extension '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                    foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                        summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                    }
                    summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
                }

                sqlBuffer.Remove(0, sqlBuffer.Length);
                sqlBuffer.Append("SELECT language, bundle, elementname, translation, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                         .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                         .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
                cmd.CommandText = sqlBuffer.ToString();
                reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                    string abbrev = reader["language"].ToString();
                    string bundle = reader["bundle"].ToString();
                    string bundleName = reader["bundlename"].ToString(); // JavaScript / XUL
                    string elementName = reader["elementname"].ToString(); // eg: element
                    if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                        if(!completionMap.ContainsKey(abbrev)) {
                            completionMap[abbrev] = 0;
                        }
                        completionMap[abbrev]++;
                        if(!resources.ContainsKey(abbrev)) { //  first element in this form name
                            resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                        }
                        if(!resources[abbrev].ContainsKey(bundleName)) {
                            resources[abbrev][bundleName] = new Dictionary<string, string>();
                        }
                        resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                    }
                }
                reader.Close();

                if(includedLanguages.Contains(defaultLanguage)) {
                    if(includeSummary) {
                        string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                        summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                    }
                    resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                    foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                        resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                        foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                            resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                        }
                    }
                }

                // remove languages below the completion threshold
                List<string> incompleteLanguages = new List<string>();
                foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                    int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                    if(completionPercentage > threshold) {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.AppendFormat("*Included {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage);
                        }
                        if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                            resources[languageCompletion.Key] = defaultLanguageStrings;
                        } else {
                            foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                                if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                    resources[languageCompletion.Key][bundle.Key] = bundle.Value;
                                } else {
                                    foreach(KeyValuePair<string, string> element in bundle.Value) {
                                        if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                            resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                        }
                                    }
                                }
                            }
                        }
                    } else {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.AppendFormat("*Omitted {0} ({1}), {2}% complete\n", languageCompletion.Key, languageName, completionPercentage);
                        }
                        resources.Remove(languageCompletion.Key);
                    }
                }
            }
            using(MemoryStream ms = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(ms);
                zipStream.SetLevel(9);
                Crc32 crc = new Crc32();
                StringBuilder resourceBuffer;

                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
                    /* language.Key = language abbreviation
                       language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
                    ZipEntry zipEntry = new ZipEntry(language.Key + "\\contents.rdf");
                    string cultureName = Utils.GetSafeEnglishName(language.Key);
                    resourceBuffer = new StringBuilder();
                    resourceBuffer.Append(string.Format(Constants.ContentsRDF, language.Key, cultureName, applicationName));
                    zipEntry.DateTime = DateTime.Now;
                    byte[] buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                    zipEntry.Size = buffer.Length;

                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);

                    foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                        /* bundle.Key = bundleName
                           bundle.Value = Dictionary<element, resource> */
                        resourceBuffer = new StringBuilder();
                        switch(bundle.Key.ToLower()) {
                            case "javascript":
                                zipEntry = new ZipEntry(language.Key + "\\" + Utils.SanitizeFilename(applicationName) + ".properties");
                                foreach(KeyValuePair<string, string> jsElement in bundle.Value) {
                                    resourceBuffer.Append(jsElement.Key).Append("=").Append(escapeUnicode ? Utils.UnicodeEscape(jsElement.Value) : jsElement.Value).Append("\n");
                                }
                                break;
                            case "xul":
                                zipEntry = new ZipEntry(language.Key + "\\" + Utils.SanitizeFilename(applicationName) + ".dtd");
                                foreach(KeyValuePair<string, string> xulElement in bundle.Value) {
                                    resourceBuffer.Append("<!ENTITY ").Append(xulElement.Key).Append(" \"").Append(xulElement.Value).Append("\">\n");
                                }
                                break;
                        }

                        zipEntry.DateTime = DateTime.Now;
                        buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                    }
                }

                if(includeSummary) {
                    ZipEntry zipEntry = new ZipEntry("summary.txt");
                    zipEntry.DateTime = DateTime.Now;
                    byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
                    zipEntry.Size = summaryBytes.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                }
                zipStream.Finish();
                SaveZip(ms, file, !zipOutput);
                ms.Close();
            }
        }

        /// <summary>
        /// Outputs to .NET resx files.
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="threshold">The completion threshold.</param>
        /// <param name="includedBundles">The bundles to include.</param>
        /// <param name="includedLanguages">The languages to include.</param>
        /// <param name="defaultLanguage">The default language.</param>
        /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
        /// <param name="generateNeutral">if set to <c>true</c>, for each bundle additional files will be generated with no language codes to be used as the neutral/default language.</param>
        /// <param name="compactFramework"><c>true</c> if the target platform is the Compact Framework, otherwise <c>false</c>.</param>
        public static void GenerateDotNet(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool generateNeutral, bool compactFramework) {
            KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
            Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> forms = rawResources.Value;

            using(MemoryStream ms = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(ms);
                zipStream.SetLevel(9);
                Crc32 crc = new Crc32();

                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> form in forms) {
                    /* form.Key = bundlename
                       form.Value = Dictionary<abbrev, Dictionary<location, translation>> */
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in form.Value) {
                        /* lang.Key = abbrev
                           lang.Value = Dictionary<location, translation> */
                        XmlDocument resx = new XmlDocument();
                        resx.LoadXml(Constants.ResxSkeleton);
                        XmlNode root = resx.SelectSingleNode("/root");

                        string langAbbreviation = string.Empty;
                        if(compactFramework) {
                            switch(lang.Key) {
                                case "zh-Hans": // buggy .NET Framework and contradictory MSDN articles
                                    langAbbreviation = "zh-CHS";
                                    break;
                                case "zh-Hant": // buggy .NET Framework and contradictory MSDN articles
                                    langAbbreviation = "zh-CHT";
                                    break;
                                default:
                                    langAbbreviation = lang.Key;
                                    break;
                            }
                        } else {
                            langAbbreviation = lang.Key;
                        }
                        foreach(KeyValuePair<string, RBTranslation> translatedResource in lang.Value) {
                            XmlNode nodeResourceData = resx.CreateElement("data");
                            nodeResourceData.Attributes.SetNamedItem(resx.CreateAttribute("name")).InnerText = translatedResource.Key;
                            nodeResourceData.AppendChild(resx.CreateElement("value")).InnerText = translatedResource.Value.Translation.Replace("\\n", "\n");
                            if(translatedResource.Value.Comment.Length > 0) {
                                nodeResourceData.AppendChild(resx.CreateElement("comment")).InnerText = translatedResource.Value.Comment.Replace("\\n", "\n");
                            }
                            root.AppendChild(nodeResourceData);
                        }

                        ZipEntry zipEntry;
                        using(MemoryStream msEntry = new MemoryStream()) {
                            zipEntry = new ZipEntry(Utils.SanitizeFilename(form.Key + "." + langAbbreviation + ".resx"));
                            zipEntry.DateTime = DateTime.Now;
                            resx.Save(msEntry);
                            byte[] buffer = msEntry.ToArray();
                            msEntry.Close();
                            msEntry.Dispose();
                            zipEntry.Size = buffer.Length;

                            zipStream.PutNextEntry(zipEntry);
                            zipStream.Write(buffer, 0, buffer.Length);

                            if(generateNeutral && lang.Key == defaultLanguage) {
                                zipEntry = new ZipEntry(Utils.SanitizeFilename(form.Key + ".resx"));
                                zipEntry.DateTime = DateTime.Now;
                                zipEntry.Size = buffer.Length;
                                zipStream.PutNextEntry(zipEntry);
                                zipStream.Write(buffer, 0, buffer.Length);
                            }
                        }
                    }
                }

                if(includeSummary) {
                    ZipEntry zipEntry = new ZipEntry("summary.txt");
                    zipEntry.DateTime = DateTime.Now;
                    byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key.ToString());
                    zipEntry.Size = summaryBytes.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                }
                zipStream.Finish();
                SaveZip(ms, file, !zipOutput);
                ms.Close();
            }
        }


        /// <summary>
        /// Outputs bundles to Java .properties files. This could be done using a template, but Java uses subtly different language codes
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="threshold">The completion threshold.</param>
        /// <param name="includedBundles">The bundles to include.</param>
        /// <param name="includedLanguages">The languages to include.</param>
        /// <param name="defaultLanguage">The default language.</param>
        /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
        /// <param name="generateNeutral">if set to <c>true</c>, for each bundle additional files will be generated with no language codes to be used as the neutral/default language.</param>
        /// <param name="escapeUnicode"><c>true</c> if code points above 127 (7F) should be replaced with their unicode escape sequences (\uXXXX).</param>
        public static void GenerateJava(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary, bool generateNeutral, bool escapeUnicode) {
            KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources = GenerateBundles(applicationName, applicationId, threshold, includedBundles, includedLanguages, defaultLanguage, includeSummary);
            using(MemoryStream ms = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(ms);
                zipStream.SetLevel(9);
                Crc32 crc = new Crc32();
                StringBuilder resourceBuffer;
                ZipEntry zipEntry;
                byte[] buffer;

                Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = rawResources.Value;
                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundle in bundles) {
                    /* bundle.Key = bundlename
                       bundle.Value = Dictionary<abbrev, Dictionary<element, resource>> */
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in bundle.Value) {
                        /* lang.Key = abbrev
                           lang.Value = Dictionary<element, resource> */
                        resourceBuffer = new StringBuilder();
                        string langAbbreviation = Utils.DotNetCulture2JavaCulture(lang.Key);

                        zipEntry = new ZipEntry(Utils.SanitizeFilename(bundle.Key + "_" + langAbbreviation + ".properties"));
                        zipEntry.DateTime = DateTime.Now;
                        foreach(KeyValuePair<string, RBTranslation> resource in lang.Value) {
                            resourceBuffer.Append(resource.Key).Append("=").AppendLine(escapeUnicode ? Utils.UnicodeEscape(resource.Value.Translation) : resource.Value.Translation);
                        }
                        buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                        zipEntry.Size = buffer.Length;
                        zipEntry.IsUnicodeText = true;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);

                        if(generateNeutral && lang.Key == defaultLanguage) {
                            zipEntry = new ZipEntry(Utils.SanitizeFilename(bundle.Key + ".properties"));
                            zipEntry.DateTime = DateTime.Now;
                            zipEntry.Size = buffer.Length;
                            zipStream.PutNextEntry(zipEntry);
                            zipStream.Write(buffer, 0, buffer.Length);
                        }
                    }
                }

                if(rawResources.Key != null) {
                    zipEntry = new ZipEntry("summary.txt");
                    zipEntry.DateTime = DateTime.Now;
                    byte[] summaryBytes = Encoding.UTF8.GetBytes(rawResources.Key);
                    zipEntry.Size = summaryBytes.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                }
                zipStream.Finish();
                SaveZip(ms, file, !zipOutput);
                ms.Close();
            }
        }

        /// <summary>
        /// Generates resources using a template.
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="rawResources">The raw resources.</param>
        /// <param name="template">The template to use.</param>
        public static void GenerateFromTemplate(FileInfo file, bool zipOutput, string applicationName, KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources, RBTemplate template) {
            GenerateCustom(file, zipOutput, applicationName, rawResources, template.FileName, template.FileStart, template.FileEnd, template.FileLine);
        }

        /// <summary>
        /// Generates resources using a custom template.
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="rawResources">The raw resources.</param>
        /// <param name="fileName">Name of each file.</param>
        /// <param name="fileStart">The text to prepend to the start of each file.</param>
        /// <param name="fileEnd">The text to append to the end of each file.</param>
        /// <param name="lineFormat">The line format.</param>
        public static void GenerateCustom(FileInfo file, bool zipOutput, string applicationName, KeyValuePair<string, Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>>> rawResources, string fileName, string fileStart, string fileEnd, string lineFormat) {
            using(MemoryStream ms = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(ms);
                zipStream.SetLevel(9);
                Crc32 crc = new Crc32();
                StringBuilder resourceBuffer;
                ZipEntry zipEntry;
                byte[] buffer;

                string[] bundleTags = new string[] { "{=LanguageCode}",
                                                     "{=Bundle}" }, // valid only for the current bundle (filename, filestart, fileend)
                         elementTags = new string[] { "{=LanguageCode}",
                                                      "{=Bundle}",
                                                      "{=Element}",
                                                      "{=Translation}"
                                                       }, // valid for each element
                         replacementBundleTags = new string[] { "", "" },
                         replacementElementTags = new string[] { "", "", "", "" };
                Dictionary<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundles = rawResources.Value;
                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, RBTranslation>>> bundle in bundles) {
                    /* bundle.Key = bundlename
                       bundle.Value = Dictionary<abbrev, Dictionary<element, translation>> */
                    replacementBundleTags[1] = bundle.Key;
                    replacementElementTags[1] = bundle.Key;
                    foreach(KeyValuePair<string, Dictionary<string, RBTranslation>> lang in bundle.Value) {
                        /* lang.Key = abbrev
                           lang.Value = Dictionary<element, translation> */
                        replacementBundleTags[0] = lang.Key;
                        replacementElementTags[0] = lang.Key;
                        resourceBuffer = new StringBuilder();
                        if(fileStart.Length > 0) {
                            resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileStart, bundleTags, replacementBundleTags));
                        }
                        zipEntry = new ZipEntry(Utils.SanitizeFilename(Utils.FormatTemplateTags(fileName, bundleTags, replacementBundleTags)));
                        zipEntry.DateTime = DateTime.Now;
                        foreach(KeyValuePair<string, RBTranslation> resource in lang.Value) {
                            replacementElementTags[2] = resource.Key;
                            replacementElementTags[3] = resource.Value.Translation;
                            resourceBuffer.AppendLine(Utils.FormatTemplateTags(lineFormat, elementTags, replacementElementTags));
                        }
                        if(fileEnd.Length > 0) {
                            resourceBuffer.AppendLine(Utils.FormatTemplateTags(fileEnd, bundleTags, replacementBundleTags));
                        }
                        buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                    }
                }

                string summary = rawResources.Key;
                if(summary != null) {
                    zipEntry = new ZipEntry("summary.txt");
                    zipEntry.DateTime = DateTime.Now;
                    byte[] summaryBytes = Encoding.UTF8.GetBytes(summary);
                    zipEntry.Size = summaryBytes.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                }
                zipStream.Finish();
                SaveZip(ms, file, !zipOutput);
                ms.Close();
            }
        }

        /// <summary>
        /// Outputs to GNU PO files.
        /// </summary>
        /// <param name="file">The file to generate.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped, otherwise <c>false</c>.</param>
        /// <param name="applicationName">Name of the application.</param>
        /// <param name="applicationId">The application ID.</param>
        /// <param name="threshold">The completion threshold.</param>
        /// <param name="includedBundles">The bundles to include.</param>
        /// <param name="includedLanguages">The languages to include.</param>
        /// <param name="defaultLanguage">The default language.</param>
        /// <param name="includeSummary"><c>true</c> if a summary of generated resources should be included in the zip, otherwise <c>false</c>.</param>
        public static void GeneratePO(FileInfo file, bool zipOutput, string applicationName, string applicationId, int threshold, string[] includedBundles, string[] includedLanguages, string defaultLanguage, bool includeSummary) {
            Dictionary<string, Dictionary<string, Dictionary<string, string>>> resources = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>(); // <BundleName, <abbrev, <elementname, translation>>>
            List<string> errors = new List<string>();
            StringBuilder summary = null;
            if(includeSummary) {
                summary = new StringBuilder("Resources generated by ResourceBlender v").Append(Constants.GetVersion().ToString(3))
                                    .Append(" on ").Append(DateTime.Now.ToShortDateString()).Append("\n");
            }

            Dictionary<string, int> completionMap = new Dictionary<string, int>();
            int totalStrings = 0;
            Dictionary<string, Dictionary<string, POMetaData>> elementData = new Dictionary<string, Dictionary<string, POMetaData>>(); // <BundleName, <ElementName, Element meta-data>>
            Dictionary<string, Dictionary<string, string>> defaultLanguageStrings = new Dictionary<string, Dictionary<string, string>>(); // <BundleName, <ElementName, Untranslated string>>
            Database db = new Database();
            db.OpenConnection();
            using(Command cmd = db.CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder("SELECT language, bundle, elementname, translatorcomment, comment AS metadata, translation, b.name AS bundlename FROM ");
                sqlBuffer.Append(db.TablePrefix).Append("elements AS e INNER JOIN ").Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language = @defaultLang").Append(db.GenerateInClause("bundle", includedBundles, true));
                cmd.CommandText = sqlBuffer.ToString();
                cmd.AddParameterWithValue("@applicationId", applicationId);
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                DbDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for entries in the default language (untranslated strings)
                    string bundleName = reader["bundlename"].ToString(),
                           elementName = reader["elementname"].ToString(),
                           rawMetaData = reader["metadata"].ToString(),
                           translation = reader["translation"].ToString(),
                           translatorComment = reader["translatorcomment"].ToString();
                    if(!defaultLanguageStrings.ContainsKey(bundleName)) { //  first element in this bundle
                        defaultLanguageStrings[bundleName] = new Dictionary<string, string>();
                        elementData[bundleName] = new Dictionary<string, POMetaData>();
                    }
                    defaultLanguageStrings[bundleName][elementName] = translation;
                    POMetaData data = POMetaData.Parse(rawMetaData);
                    if(translatorComment.Length > 0) {
                        data.TranslatorsComments.Add(defaultLanguage, translatorComment);
                    }
                    data.UntranslatedString = translation;
                    elementData[bundleName][elementName] = data;
                    totalStrings++;
                }
                reader.Close();
                if(includeSummary) {
                    summary.AppendFormat("Application '{0}' contains {1} element{2} in {3} bundle{4}:\n", applicationName, totalStrings, totalStrings == 1 ? "" : "s", defaultLanguageStrings.Count, defaultLanguageStrings.Count == 1 ? "" : "s");
                    foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) {
                        summary.AppendFormat("*{0} ({1} element{2})\n", bundle.Key, bundle.Value.Count, bundle.Value.Count == 1 ? "" : "s");
                    }
                    summary.AppendFormat("\nOmitting languages less than {0}% complete\n", threshold);
                }

                sqlBuffer.Remove(0, sqlBuffer.Length);
                sqlBuffer.Append("SELECT language, bundle, elementname, translation, translatorcomment, b.name AS bundlename FROM ").Append(db.TablePrefix).Append("elements AS e INNER JOIN ")
                         .Append(db.TablePrefix).Append("resources AS r ON r.resourceid = e.resourceid LEFT JOIN ").Append(db.TablePrefix).Append("bundles AS b ON e.bundle = b.id WHERE applicationid = @applicationId AND language != @defaultLang")
                         .Append(db.GenerateInClause("language", includedLanguages, true)).Append(db.GenerateInClause("bundle", includedBundles, true));
                cmd.CommandText = sqlBuffer.ToString();
                reader = cmd.ExecuteReader();
                while(reader.Read()) { // generate a dictionary for each language, fill it with entries we have
                    string abbrev = reader["language"].ToString();
                    string bundle = reader["bundle"].ToString();
                    string bundleName = reader["bundlename"].ToString();
                    string elementName = reader["elementname"].ToString(); // eg: element
                    string translatorComment = reader["translatorcomment"].ToString();
                    if(!defaultLanguageStrings.ContainsKey(bundleName) || !defaultLanguageStrings[bundleName].ContainsKey(elementName)) {
                        continue; // the default language is missing, so just skip this element
                    }
                    if(includedBundles.Contains(bundle) && includedLanguages.Contains(abbrev)) {
                        if(!completionMap.ContainsKey(abbrev)) {
                            completionMap[abbrev] = 0;
                        }
                        completionMap[abbrev]++;
                        if(!resources.ContainsKey(abbrev)) { // first element in this bundle
                            resources[abbrev] = new Dictionary<string, Dictionary<string, string>>();
                        }
                        if(!resources[abbrev].ContainsKey(bundleName)) {
                            resources[abbrev][bundleName] = new Dictionary<string, string>();
                        }
                        resources[abbrev][bundleName][elementName] = reader["translation"].ToString();
                        if(translatorComment.Length > 0) {
                            elementData[bundleName][elementName].TranslatorsComments.Add(abbrev, translatorComment);
                        }
                    }
                }
                reader.Close();

                if(includedLanguages.Contains(defaultLanguage)) {
                    if(includeSummary) {
                        string defaultLanguageName = Utils.GetSafeEnglishName(defaultLanguage);
                        summary.AppendFormat("*Included {0} ({1}), 100% complete\n", defaultLanguageName, defaultLanguage);
                    }
                    resources[defaultLanguage] = new Dictionary<string, Dictionary<string, string>>();
                    foreach(KeyValuePair<string, Dictionary<string, string>> defaultBundle in defaultLanguageStrings) {
                        resources[defaultLanguage][defaultBundle.Key] = new Dictionary<string, string>();
                        foreach(KeyValuePair<string, string> element in defaultBundle.Value) {
                            resources[defaultLanguage][defaultBundle.Key][element.Key] = element.Value;
                        }
                    }
                }

                // remove languages below the completion threshold
                List<string> incompleteLanguages = new List<string>();
                foreach(KeyValuePair<string, int> languageCompletion in completionMap) { // key=abbrev, val=translatedStrings
                    int completionPercentage = (int)Math.Round((languageCompletion.Value / (double)totalStrings) * 100);
                    if(completionPercentage > threshold) {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.AppendFormat("*Included {0} ({1}), {2}% complete\n", languageName, languageCompletion.Key, completionPercentage > 100 ? 100 : completionPercentage);
                        }
                        if(!resources.ContainsKey(languageCompletion.Key)) { // there are no resources for this language, use the default language instead
                            resources[languageCompletion.Key] = resources[defaultLanguage];
                        } else {
                            foreach(KeyValuePair<string, Dictionary<string, string>> bundle in defaultLanguageStrings) { // <BundleName, <ElementName, Meta-Data>>
                                if(!resources[languageCompletion.Key].ContainsKey(bundle.Key)) { // some resources exist, but this bundle does not, use the default language for this bundle
                                    resources[languageCompletion.Key][bundle.Key] = defaultLanguageStrings[bundle.Key];
                                } else {
                                    foreach(KeyValuePair<string, string> element in bundle.Value) {
                                        if(!resources[languageCompletion.Key][bundle.Key].ContainsKey(element.Key)) {
                                            resources[languageCompletion.Key][bundle.Key].Add(element.Key, element.Value);
                                        }
                                    }
                                }
                            }
                        }
                    } else {
                        if(includeSummary) {
                            string languageName = Utils.GetSafeEnglishName(languageCompletion.Key);
                            summary.AppendFormat("*Omitted {0} ({1}), {2}% complete\n", languageCompletion.Key, languageName, completionPercentage > 100 ? 100 : completionPercentage);
                        }
                        resources.Remove(languageCompletion.Key);
                    }
                }
            }

            using(MemoryStream ms = new MemoryStream()) {
                ZipOutputStream zipStream = new ZipOutputStream(ms);
                zipStream.SetLevel(9);
                Crc32 crc = new Crc32();
                StringBuilder resourceBuffer = new StringBuilder(); ;

                foreach(KeyValuePair<string, Dictionary<string, Dictionary<string, string>>> language in resources) {
                    /* language.Key = language abbreviation
                       language.Value = Dictionary<bundleName, Dictionary<element, resource>> */
                    ZipEntry zipEntry;
                    byte[] buffer;
                    foreach(KeyValuePair<string, Dictionary<string, string>> bundle in language.Value) {
                        /* bundle.Key = bundleName
                           bundle.Value = Dictionary<element, resource> */
                        resourceBuffer.Remove(0, resourceBuffer.Length);
                        zipEntry = new ZipEntry(Utils.SanitizeFilename(bundle.Key) + "\\" + Utils.SanitizeFilename(language.Key) + ".po");
                        foreach(KeyValuePair<string, string> element in bundle.Value) {
                            POMetaData metaData = elementData[bundle.Key][element.Key];
                            if(resourceBuffer.Length > 0) { // omit whitespace at start of file
                                resourceBuffer.Append("\n\n");
                            }
                            if(metaData.TranslatorsComments.ContainsKey(language.Key)) {
                                string translatorComments = metaData.TranslatorsComments[language.Key];
                                if(translatorComments.Length > 0) {
                                    foreach(string commentLine in translatorComments.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries)) {
                                        resourceBuffer.Append("# ").Append(commentLine).Append("\n");
                                    }
                                }
                            }
                            if(metaData.ExtractedComments.Count > 0) {
                                foreach(string comment in metaData.ExtractedComments) {
                                    resourceBuffer.Append("#. ").Append(comment).Append("\n");
                                }
                            }
                            if(metaData.References.Count > 0) {
                                foreach(string reference in metaData.References) {
                                    resourceBuffer.Append("#: ").Append(reference).Append("\n");
                                }
                            }
                            if(metaData.Flags.Count > 0) {
                                foreach(string flag in metaData.Flags) {
                                    resourceBuffer.Append("#, ").Append(flag).Append("\n");
                                }
                            }
                            if(metaData.Context != null) {
                                resourceBuffer.Append("msgctxt \"").Append(metaData.Context).Append("\"\n");
                            }
                            string msgId = element.Key == "Generator" ? "" : metaData.UntranslatedString.Replace("\\n", "\\n\"\n\"");
                            if(msgId.EndsWith("\n\"")) {
                                msgId = msgId.Substring(0, msgId.Length - 2);
                            }
                            string msgStr = element.Value.Contains("\\n") ? element.Value.Replace("\\n", "\\n\"\n\"") : element.Value; // if it's a multiline string, replace linefeeds with \n"<newline>"
                            if(msgStr.EndsWith("\n\"")) {
                                msgStr = msgStr.Substring(0, msgStr.Length - 3);
                            }
                            resourceBuffer.AppendFormat("msgid \"{0}\"\nmsgstr \"{1}\"", msgId, msgStr);
                        }

                        zipEntry.DateTime = DateTime.Now;
                        buffer = Encoding.UTF8.GetBytes(resourceBuffer.ToString());
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                    }

                    if(includeSummary) {
                        zipEntry = new ZipEntry("summary.txt");
                        zipEntry.DateTime = DateTime.Now;
                        byte[] summaryBytes = Encoding.UTF8.GetBytes(summary.ToString());
                        zipEntry.Size = summaryBytes.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(summaryBytes, 0, summaryBytes.Length);
                    }
                }
                zipStream.Finish();
                SaveZip(ms, file, !zipOutput);
                ms.Close();
            }
        }

        /// <summary>
        /// Exports to ResourceBlender XML format.
        /// </summary>
        /// <param name="log">The delegate to use to log status messages.</param>
        /// <param name="file">The file to which to export.</param>
        /// <param name="zipOutput"><c>true</c> if the output should be zipped</param>
        /// <param name="includeApps"><c>true</c> if applications should be included</param>
        /// <param name="includeElements"><c>true</c> if elements should be included</param>
        /// <param name="includeResources"><c>true</c> if resources should be included</param>
        /// <param name="includeTemplates"><c>true</c> if templates should be included</param>
        /// <param name="includeUsers"><c>true</c> if users should be included</param>
        public static void GenerateXML(StatusMessage.StatusChangedEventHandler log, FileInfo file, bool zipOutput, bool includeApps, bool includeElements, bool includeResources, bool includeTemplates, bool includeUsers) {
            int numApps = 0,
                numBundles = 0,
                numElements = 0,
                numResources = 0,
                numTemplates = 0,
                numTranslations = 0,
                numUsers = 0;
            Database db = null;
            try {
                db = new Database();
                db.OpenConnection();
                XmlDataDocument doc = new XmlDataDocument();
                XmlDeclaration decl = (XmlDeclaration)doc.CreateNode(XmlNodeType.XmlDeclaration, "", "");
                decl.Encoding = "UTF-8";
                doc.AppendChild(decl);
                XmlNode root = doc.CreateElement("resourceblender");
                doc.AppendChild(root);

                XmlElement ndMeta = doc.CreateElement("meta");
                root.AppendChild(ndMeta);
                XmlElement ndGenerator = doc.CreateElement("generator");
                ndGenerator.InnerText = "ResourceBlender Express " + Constants.GetVersion().ToString(3);
                ndMeta.AppendChild(ndGenerator);
                XmlElement ndGenerationDate = doc.CreateElement("generated");
                ndGenerationDate.InnerText = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss G\\MT");
                ndMeta.AppendChild(ndGenerationDate);

                if(includeApps) {
                    log(new StatusMessage("Exporting applications...", StatusMessage.StatusMessageType.Status));
                    using(Command cmd = db.CreateCommand()) {
                        XmlElement ndApplications = doc.CreateElement("applications");
                        root.AppendChild(ndApplications);
                        cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "applications";
                        DbDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            XmlElement eleApplication = doc.CreateElement("application");
                            ndApplications.AppendChild(eleApplication);

                            XmlElement eleId = doc.CreateElement("id");
                            eleId.InnerText = HtmlUtils.HtmlEncode(reader["id"].ToString());
                            eleApplication.AppendChild(eleId);

                            XmlElement eleName = doc.CreateElement("name");
                            eleName.InnerText = HtmlUtils.HtmlEncode(reader["name"].ToString());
                            eleApplication.AppendChild(eleName);

                            XmlElement eleDisplayname = doc.CreateElement("displayname");
                            eleDisplayname.InnerText = HtmlUtils.HtmlEncode(reader["displayname"].ToString());
                            eleApplication.AppendChild(eleDisplayname);

                            XmlElement eleDefaultLanguage = doc.CreateElement("defaultlanguage");
                            eleDefaultLanguage.InnerText = HtmlUtils.HtmlEncode(reader["defaultlanguage"].ToString());
                            eleApplication.AppendChild(eleDefaultLanguage);

                            XmlElement eleLanguages = doc.CreateElement("languages");
                            eleLanguages.InnerText = HtmlUtils.HtmlEncode(reader["languages"].ToString());
                            eleApplication.AppendChild(eleLanguages);
                            numApps++;
                        }
                        reader.Close();
                        XmlElement ndBundles = doc.CreateElement("bundles");
                        root.AppendChild(ndBundles);
                        cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "bundles";
                        reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            XmlElement eleBundle = doc.CreateElement("bundle");
                            ndBundles.AppendChild(eleBundle);

                            XmlElement eleId = doc.CreateElement("id");
                            eleId.InnerText = HtmlUtils.HtmlEncode(reader["id"].ToString());
                            eleBundle.AppendChild(eleId);

                            XmlElement eleName = doc.CreateElement("name");
                            eleName.InnerText = HtmlUtils.HtmlEncode(reader["name"].ToString());
                            eleBundle.AppendChild(eleName);

                            XmlElement eleApplicationId = doc.CreateElement("application");
                            eleApplicationId.InnerText = HtmlUtils.HtmlEncode(reader["application"].ToString());
                            eleBundle.AppendChild(eleApplicationId);
                            numBundles++;
                        }
                        reader.Close();
                    }
                }
                if(includeElements) {
                    log(new StatusMessage("Exporting elements...", StatusMessage.StatusMessageType.Status));
                    using(Command cmd = db.CreateCommand()) {
                        XmlElement ndElements = doc.CreateElement("elements");
                        root.AppendChild(ndElements);
                        cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "elements";
                        DbDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            XmlElement eleElement = doc.CreateElement("element");
                            ndElements.AppendChild(eleElement);

                            XmlElement eleApplicationId = doc.CreateElement("applicationid");
                            eleApplicationId.InnerText = HtmlUtils.HtmlEncode(reader["applicationid"].ToString());
                            eleElement.AppendChild(eleApplicationId);

                            XmlElement eleBundleName = doc.CreateElement("bundle");
                            eleBundleName.InnerText = HtmlUtils.HtmlEncode(reader["bundle"].ToString());
                            eleElement.AppendChild(eleBundleName);

                            XmlElement eleElementName = doc.CreateElement("elementname");
                            eleElementName.InnerText = HtmlUtils.HtmlEncode(reader["elementname"].ToString());
                            eleElement.AppendChild(eleElementName);

                            XmlElement eleResourceId = doc.CreateElement("resourceid");
                            eleResourceId.InnerText = reader["resourceid"].ToString();
                            eleElement.AppendChild(eleResourceId);

                            XmlElement eleComment = doc.CreateElement("comment");
                            eleComment.InnerText = HtmlUtils.HtmlEncode(reader["comment"].ToString());
                            eleElement.AppendChild(eleComment);
                            numElements++;
                        }
                        reader.Close();
                    }
                }
                if(includeResources) {
                    log(new StatusMessage("Exporting resources...", StatusMessage.StatusMessageType.Status));
                    using(Command cmd = db.CreateCommand()) {
                        XmlElement ndResources = doc.CreateElement("resources");
                        root.AppendChild(ndResources);
                        cmd.CommandText = "SELECT r.resourceid AS resourceid, language, translation, translatorcomment, comment FROM " + db.TablePrefix + "resources AS r LEFT JOIN " + db.TablePrefix + "resourcecomments AS c ON r.resourceid = c.resourceid ORDER BY r.resourceid ASC";
                        DbDataReader reader = cmd.ExecuteReader();
                        string lastResourceId = string.Empty,
                               currentResourceId = string.Empty;
                        XmlElement eleResource = null;
                        while(reader.Read()) {
                            currentResourceId = reader["resourceid"].ToString();
                            if(currentResourceId != lastResourceId) {
                                eleResource = doc.CreateElement("resource");
                                ndResources.AppendChild(eleResource);

                                XmlElement eleId = doc.CreateElement("id");
                                eleId.InnerText = HtmlUtils.HtmlEncode(currentResourceId);
                                eleResource.AppendChild(eleId);

                                XmlElement eleComment = doc.CreateElement("comment");
                                eleComment.InnerText = HtmlUtils.HtmlEncode(reader["comment"].ToString());
                                eleResource.AppendChild(eleComment);

                                XmlElement eleLanguages = doc.CreateElement("languages");
                                eleResource.AppendChild(eleLanguages);
                                numTranslations++;
                            }
                            lastResourceId = currentResourceId;
                            XmlElement eleTranslatedLanguage = doc.CreateElement("language");
                            eleTranslatedLanguage.InnerText = HtmlUtils.HtmlEncode(reader["translation"].ToString());
                            eleResource.LastChild.AppendChild(eleTranslatedLanguage);

                            XmlAttribute attrAbbreviation = doc.CreateAttribute("code");
                            attrAbbreviation.InnerText = reader["language"].ToString();
                            eleTranslatedLanguage.Attributes.Append(attrAbbreviation);

                            string translatorComment = reader["translatorcomment"].ToString();
                            if(translatorComment.Length > 0) {
                                XmlAttribute attrTranslatorComment = doc.CreateAttribute("translatorcomment");
                                attrTranslatorComment.InnerText = HtmlUtils.HtmlEncode(translatorComment);
                                eleTranslatedLanguage.Attributes.Append(attrTranslatorComment);
                            }
                            numResources++;
                        }
                        reader.Close();
                    }
                }
                if(includeTemplates) {
                    log(new StatusMessage("Exporting templates...", StatusMessage.StatusMessageType.Status));
                    using(Command cmd = db.CreateCommand()) {
                        XmlElement ndElements = doc.CreateElement("templates");
                        root.AppendChild(ndElements);
                        cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "templates";
                        DbDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            XmlElement eleElement = doc.CreateElement("template");
                            ndElements.AppendChild(eleElement);

                            XmlElement eleTemplateName = doc.CreateElement("name");
                            eleTemplateName.InnerText = HtmlUtils.HtmlEncode(reader["name"].ToString());
                            eleElement.AppendChild(eleTemplateName);

                            XmlElement eleFileName = doc.CreateElement("filename");
                            eleFileName.InnerText = HtmlUtils.HtmlEncode(reader["filename"].ToString());
                            eleElement.AppendChild(eleFileName);

                            XmlElement eleFileStart = doc.CreateElement("filestart");
                            eleFileStart.InnerText = HtmlUtils.HtmlEncode(reader["filestart"].ToString());
                            eleElement.AppendChild(eleFileStart);

                            XmlElement eleFileEnd = doc.CreateElement("fileend");
                            eleFileEnd.InnerText = HtmlUtils.HtmlEncode(reader["fileend"].ToString());
                            eleElement.AppendChild(eleFileEnd);

                            XmlElement eleLine = doc.CreateElement("line");
                            eleLine.InnerText = HtmlUtils.HtmlEncode(reader["line"].ToString());
                            eleElement.AppendChild(eleLine);
                            numTemplates++;
                        }
                        reader.Close();
                    }
                }
                if(includeUsers) {
                    log(new StatusMessage("Exporting users...", StatusMessage.StatusMessageType.Status));
                    using(Command cmd = db.CreateCommand()) {
                        XmlElement ndUsers = doc.CreateElement("users");
                        root.AppendChild(ndUsers);
                        cmd.CommandText = "SELECT * FROM " + db.TablePrefix + "users";
                        DbDataReader reader = cmd.ExecuteReader();
                        while(reader.Read()) {
                            XmlElement eleUser = doc.CreateElement("user");
                            ndUsers.AppendChild(eleUser);

                            XmlElement eleId = doc.CreateElement("id");
                            eleId.InnerText = HtmlUtils.HtmlEncode(reader["id"].ToString());
                            eleUser.AppendChild(eleId);

                            XmlElement eleUsername = doc.CreateElement("username");
                            eleUsername.InnerText = HtmlUtils.HtmlEncode(reader["username"].ToString());
                            eleUser.AppendChild(eleUsername);

                            XmlElement elePassword = doc.CreateElement("password");
                            elePassword.InnerText = HtmlUtils.HtmlEncode(reader["password"].ToString());
                            eleUser.AppendChild(elePassword);

                            XmlElement eleEmail = doc.CreateElement("email");
                            eleEmail.InnerText = HtmlUtils.HtmlEncode(reader["email"].ToString());
                            eleUser.AppendChild(eleEmail);

                            XmlElement elePermissions = doc.CreateElement("permissions");
                            elePermissions.InnerText = HtmlUtils.HtmlEncode(reader["permissions"].ToString());
                            eleUser.AppendChild(elePermissions);

                            XmlElement eleAllowedProjects = doc.CreateElement("allowedprojects");
                            eleAllowedProjects.InnerText = HtmlUtils.HtmlEncode(reader["allowedprojects"].ToString());
                            eleUser.AppendChild(eleAllowedProjects);
                            numUsers++;
                        }
                        reader.Close();
                    }
                }
                log(new StatusMessage("Saving XML document...", StatusMessage.StatusMessageType.Status));
                using(MemoryStream msDoc = new MemoryStream())
                using(MemoryStream msZip = new MemoryStream()) {
                    ZipOutputStream zipStream = new ZipOutputStream(msZip);
                    zipStream.SetLevel(9);
                    doc.Save(msDoc);
                    byte[] buffer = msDoc.ToArray();
                    msDoc.Close();
                    ZipEntry zipEntry = new ZipEntry("ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ".xml");
                    zipEntry.DateTime = DateTime.Now;
                    zipEntry.Size = buffer.Length;
                    zipStream.PutNextEntry(zipEntry);
                    zipStream.Write(buffer, 0, buffer.Length);
                    zipStream.Finish();
                    SaveZip(msZip, file, !zipOutput);
                    msZip.Close();
                }
                log(new StatusMessage("Done!", StatusMessage.StatusMessageType.Success));
            } catch(Exception ex) {
                log(new StatusMessage("Error #455: " + ex.Message, StatusMessage.StatusMessageType.Error));
                throw new Exception("Error #455: " + ex.Message, ex.InnerException);
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
        }

        /// <summary>
        /// Exports completion summaries to a CSV.
        /// </summary>
        /// <param name="file">The file to which to export.</param>
        /// <param name="summaries">The list of summaries.</param>
        public static void ExportToSummaryCSV(string fileName, List<ApplicationSummary> summaries) {
            StreamWriter writer = new StreamWriter(fileName);
            writer.WriteLine("Application,EnglishName,LanguageCode,Variant,AvailableTranslations,CompletionPercentage");
            foreach(ApplicationSummary summary in summaries) {
                foreach(LanguageCompletion language in summary.CompletionMap) {
                    int completionPercentage = (int)Math.Round((language.NumTranslations / (double)summary.TotalElements) * 100.0, 0);
                    if(completionPercentage > 100) {
                        completionPercentage = 100;
                    } else if(completionPercentage < 0) {
                        completionPercentage = 0;
                    }
                    writer.WriteLine(string.Format("{0},{1},{2},{3},{4},{5}", summary.DisplayName, language.Culture.EnglishName, language.Culture.Parent == System.Globalization.CultureInfo.InvariantCulture ? language.Culture.Name : language.Culture.Parent.Name, language.Culture.Name, language.NumTranslations, completionPercentage));
                }
            }
            writer.Close();
        }

       

        /// <summary>
        /// Exports resources to an XML spreadsheet
        /// </summary>
        /// <param name="log">The delegate to use to log status messages.</param>
        /// <param name="applicationIds">The IDs of the applications to export.</param>
        /// <param name="selectedLanguages">The IDs of the languages to export.</param>
        /// <param name="selectedBundles">The IDs of the bundles to export.</param>
        /// <param name="zipOutput"><c>true</c> if the spreadsheet should be zipped, otherwise <c>false</c>.</param>
        /// <param name="file">The file to which to export.</param>
        public static void ExportToExcel(StatusMessage.StatusChangedEventHandler log, string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, bool zipOutput, FileInfo file) {
            Database db = null;
            Exception error = null;
            try {
                db = new Database();
                db.OpenConnection();
                log(new StatusMessage("Exporting resources...", StatusMessage.StatusMessageType.Status));
                ExcelXmlWorkbook wb = new ExcelXmlWorkbook();
                List<int> addedIds = new List<int>();
                wb.Properties.Title = "ResourceBlender Resources (" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ")";
                int numExported = 0;
                using(Command cmdApplication = db.CreateCommand())
                using(Command cmdResources = db.CreateCommand()) {
                    cmdApplication.CommandText = string.Format("SELECT displayname, defaultLanguage, languages FROM {0}applications WHERE id = @p", db.TablePrefix);
                    cmdApplication.AddParameterWithValue("@p", "");
                    cmdResources.AddParameterWithValue("@p", "");
                    string applicationId = null,
                           applicationName = null,
                           defaultLanguage = null;
                    string[] allLanguagesInApplication = null;
                    int startingColumn = 1;
                    List<string> selectedLanguagesInApplication = new List<string>(); // if just one list of languages is used, there are gaps for columns where a language from another application is not present in the current application because each app is on a separate sheet
                    XmlStyle greenBackground = new XmlStyle(),
                             greyBackground = new XmlStyle();
                    greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);
                    greenBackground.Interior.Pattern = Pattern.Solid;
                    greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);
                    greyBackground.Interior.Pattern = Pattern.Solid;
                    greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);
                    for(int i = 0; i < applicationIds.Length; i++) {
                        selectedLanguagesInApplication.Clear();
                        applicationId = applicationIds[i];
                        cmdApplication.Parameters["@p"].Value = applicationId;
                        cmdResources.Parameters["@p"].Value = applicationId;
                        using(DbDataReader reader = cmdApplication.ExecuteReader(CommandBehavior.SingleResult)) {
                            while(reader.Read()) {
                                applicationName = reader.GetString(0);
                                defaultLanguage = reader.GetString(1);
                                allLanguagesInApplication = reader.GetString(2).Trim().Split(',');
                            }
                            reader.Close();
                        }
                        if(applicationName == null || applicationName.Length == 0) {
                            throw new ArgumentNullException("Application not found");
                        }
                        selectedLanguagesInApplication.Add(defaultLanguage);

                        int rowIndex = 1;
                        Worksheet sheet = wb[i];
                        sheet.Name = applicationName;
                        Row row = sheet.AddRow();
                        int column = startingColumn + 1;
                        sheet[startingColumn, 0].Value = "Resource ID";
                        Cell headerCell = sheet[column++, 0];
                        headerCell.Value = defaultLanguage;
                        foreach(string lang in allLanguagesInApplication) {
                            if(selectedLanguages.Contains(lang)) {
                                if(lang != defaultLanguage) {
                                    selectedLanguagesInApplication.Add(lang);
                                    headerCell = sheet[column++, 0];
                                    headerCell.Value = lang;
                                }
                            }
                        }
                        sheet.FreezeTopRows = 1;
                        if(selectedLanguagesInApplication.Count == 0) {
                            throw new ArgumentException("No languages found for appliication:" + applicationName);
                        }

                        //sheet.Table.Columns[startingColumn].Width = 200;
                        cmdResources.CommandText = string.Format(@"SELECT DISTINCT e.resourceid, e.id, r.language, r.translation, r.translatorcomment, c.comment FROM {0}elements AS e 
                                                               INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid LEFT JOIN {0}resourcecomments AS c ON c.resourceid = r.resourceid 
                                                               WHERE applicationid = @p AND language IN('{1}') AND bundle IN('{2}') ORDER BY e.id",
                                                          db.TablePrefix, Utils.Implode(selectedLanguagesInApplication, "','", false), Utils.Implode(selectedBundles, "','", false));
                        using(DbDataReader reader = cmdResources.ExecuteReader()) {
                            int lastIdRead = -1;
                            while(reader.Read()) {
                                try {
                                    int resourceId = int.Parse(reader["resourceid"].ToString());
                                    Cell cell = null;
                                    if(lastIdRead == -1) {
                                        if(addedIds.Contains(resourceId)) {
                                            continue;
                                        }
                                        addedIds.Add(resourceId);
                                        lastIdRead = resourceId;
                                        cell = sheet[startingColumn, rowIndex];
                                        cell.Value = resourceId;
                                        cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                        cell.Style = greyBackground;
                                        numExported++;
                                    } else if(lastIdRead != resourceId) {
                                        if(addedIds.Contains(resourceId)) {
                                            continue;
                                        }
                                        addedIds.Add(resourceId);
                                        lastIdRead = resourceId;
                                        cell = sheet[startingColumn, ++rowIndex];
                                        cell.Value = resourceId;
                                        cell.Comment = reader["comment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                        cell.Style = greyBackground;
                                        numExported++;
                                    }
                                    cell = sheet[selectedLanguagesInApplication.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];
                                    cell.Value = reader["translation"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                    cell.Comment = reader["translatorcomment"].ToString().Replace("<", "&lt;").Replace(">", "&gt;");
                                    log(new StatusMessage(string.Format("Exported resource #{0}", resourceId), StatusMessage.StatusMessageType.Success));
                                } catch(Exception ex) {
                                    log(new StatusMessage(string.Format("Error saving resource: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                                }
                            }
                            reader.Close();
                        }
                        Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);
                        defaultColumn.Style = greenBackground;
                        Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);
                        headerCells.Font.Bold = true;
                    }
                }
                try {
                    using(MemoryStream msXml = new MemoryStream()) // holding stream for workbook
                    using(MemoryStream msZip = new MemoryStream()) { // output stream for zip
                        ZipOutputStream zipStream = new ZipOutputStream(msZip);
                        zipStream.SetLevel(9);

                        wb.Export(msXml);
                        byte[] buffer = msXml.ToArray();
                        msXml.Close();
                        string fileName;
                        if(zipOutput) {
                            fileName = "ResourceBlender" + DateTime.Now.ToString("yyyy-MM-dd-HHmm") + ".xml";
                        } else {
                            fileName = file.Name;
                        }
                        ZipEntry zipEntry = new ZipEntry(fileName);
                        zipEntry.DateTime = DateTime.Now;
                        zipEntry.Size = buffer.Length;
                        zipStream.PutNextEntry(zipEntry);
                        zipStream.Write(buffer, 0, buffer.Length);
                        zipStream.Finish();
                        SaveZip(msZip, file, !zipOutput);
                        msZip.Close();
                    }
                    log(new StatusMessage(string.Format("{0} resources exported.", numExported), StatusMessage.StatusMessageType.Status));
                } catch(Exception ex) {
                    log(new StatusMessage(string.Format("Error saving file: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                }
            } catch(Exception ex) {
                error = ex;
            } finally {
                if(db != null && db.Connected) {
                    db.CloseConnection();
                }
            }
            if(error != null) {
                log(new StatusMessage(string.Format("Error: {0}", error.Message), StatusMessage.StatusMessageType.Error));
                throw error; // after closing the DB connection
            }
        }

        /// <summary>
        /// Exports resources to a ResourceBlender translations database.
        /// </summary>
        /// <param name="log">The delegate to use to log status messages.</param>
        /// <param name="applicationIds">The IDs of the applications to export.</param>
        /// <param name="selectedLanguages">The IDs of the languages to export.</param>
        /// <param name="selectedBundles">The IDs of the bundles to export.</param>
        /// <param name="file">The file to which to export.</param>
        public static void ExportToRBT(StatusMessage.StatusChangedEventHandler log, string[] applicationIds, string[] selectedLanguages, string[] selectedBundles, FileInfo file) {
            Database db = null;
            Exception error = null;
            try {
                List<RBElement> elements = new List<RBElement>();
                Dictionary<int, RBResource> resources = new Dictionary<int, RBResource>();
                db = new Database();
                db.OpenConnection();

                using(Command cmd = db.CreateCommand()) {
                    log(new StatusMessage("Reading resources...", StatusMessage.StatusMessageType.Status));
                    cmd.CommandText = string.Format(@"SELECT DISTINCT e.resourceid, r.language, r.translation, r.translatorcomment, c.comment FROM {0}elements AS e 
                                                  INNER JOIN {0}resources AS r ON r.resourceid = e.resourceid LEFT JOIN {0}resourcecomments AS c ON c.resourceid = r.resourceid 
                                                  WHERE {1} {2} {3}",
                                                      db.TablePrefix,
                                                      db.GenerateInClause("applicationid", applicationIds, false),
                                                      db.GenerateInClause("language", selectedLanguages, true),
                                                      db.GenerateInClause("bundle", selectedBundles, true));
                    using(DbDataReader reader = cmd.ExecuteReader()) {
                        while(reader.Read()) {
                            try {
                                int resourceId = int.Parse(reader["resourceid"].ToString());
                                string langCode = reader["language"].ToString(),
                                       translation = reader["translation"].ToString(),
                                       translatorComment = reader["translatorcomment"].ToString();
                                RBTranslation translatedResource = new RBTranslation(langCode, translation, translatorComment);
                                if(resources.ContainsKey(resourceId)) {
                                    resources[resourceId].Translations.Add(langCode, translatedResource);
                                } else {
                                    RBResource resource = new RBResource(resourceId, reader["comment"].ToString());
                                    resource.Translations.Add(langCode, translatedResource);
                                    resources.Add(resourceId, resource);
                                }

                            } catch(Exception ex) {
                                log(new StatusMessage(string.Format("Error reading resource: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                        reader.Close();
                    }

                    cmd.CommandText = string.Format(@"SELECT a.displayname, b.name AS bundle, e.elementname, e.resourceid, e.comment FROM {0}applications AS a 
                                                  INNER JOIN {0}elements AS e ON a.id = e.applicationid LEFT JOIN {0}bundles AS b ON e.bundle = b.id WHERE resourceid = @rID", db.TablePrefix);
                    cmd.AddParameterWithValue("@rID", "");
                    foreach(KeyValuePair<int, RBResource> resource in resources) {
                        cmd.Parameters[0].Value = resource.Value.ID;
                        using(DbDataReader elementReader = cmd.ExecuteReader()) {
                            while(elementReader.Read()) {
                                RBElement element = new RBElement(resource.Value.ID, elementReader["elementname"].ToString(), null, elementReader["bundle"].ToString(), elementReader["comment"].ToString());
                                element.Application = new RBApplication(elementReader["displayname"].ToString());
                                elements.Add(element);
                            }
                            elementReader.Close();
                        }
                    }
                    db.CloseConnection();
                }

                log(new StatusMessage("Exporting resources...", StatusMessage.StatusMessageType.Status));
                int numExported = 0;
                SQLiteConnection fConn = null;
                try {
                    SQLiteConnection.CreateFile(file.FullName);
                    fConn = new SQLiteConnection(Database.GetSQLiteConnectionString(file.FullName));
                    fConn.Open();
                } catch(Exception ex) {
                    log(new StatusMessage(string.Format("Error creating file: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                    throw;
                }
                using(SQLiteTransaction trans = fConn.BeginTransaction()) {
                    using(SQLiteCommand cmdElement = fConn.CreateCommand()) {
                        cmdElement.CommandText = "CREATE TABLE elements (applicationname text(255) NOT NULL, bundle text(10) NOT NULL, elementname text(255), resourceid int(10), comment text, UNIQUE (applicationname,elementname,bundle));";
                        cmdElement.ExecuteNonQuery();

                        cmdElement.CommandText = "INSERT INTO elements (applicationname, bundle, elementname, resourceid, comment) VALUES (@appName, @bundle,  @elementName, @resourceId, @comment);";
                        cmdElement.Parameters.AddWithValue("@appName", "");
                        cmdElement.Parameters.AddWithValue("@bundle", "");
                        cmdElement.Parameters.AddWithValue("@elementName", "");
                        cmdElement.Parameters.AddWithValue("@resourceId", "");
                        cmdElement.Parameters.AddWithValue("@comment", "");

                        foreach(RBElement element in elements) {
                            try {
                                cmdElement.Parameters["@appName"].Value = element.Application.DisplayName;
                                cmdElement.Parameters["@bundle"].Value = element.BundleName;
                                cmdElement.Parameters["@elementName"].Value = element.Name;
                                cmdElement.Parameters["@resourceId"].Value = element.ID;
                                cmdElement.Parameters["@comment"].Value = element.Comment;
                                cmdElement.ExecuteNonQuery();
                                log(new StatusMessage(string.Format("Exported element '{0}'", element.Name), StatusMessage.StatusMessageType.Success));
                            } catch(Exception ex) {
                                log(new StatusMessage(string.Format("Error saving element: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                    }
                    using(SQLiteCommand cmdResource = fConn.CreateCommand())
                    using(SQLiteCommand cmdResourceComment = fConn.CreateCommand()) {
                        // if there are no resources for a particular language, a SELECT DISTINCT will fail, so store the languages in a separate table
                        cmdResource.CommandText = "CREATE TABLE languages (language text(10) PRIMARY KEY NOT NULL);";
                        cmdResource.ExecuteNonQuery();
                        cmdResource.CommandText = "INSERT INTO languages (language) VALUES (@language);";
                        cmdResource.Parameters.AddWithValue("@language", "");
                        foreach(string langCode in selectedLanguages) {
                            cmdResource.Parameters[0].Value = langCode;
                            cmdResource.ExecuteNonQuery();
                        }
                        cmdResource.Parameters.Clear();

                        cmdResource.CommandText = "CREATE TABLE resources (resourceid int(10) NOT NULL, language text(10) NOT NULL, translation text NOT NULL, translatorcomment text, PRIMARY KEY (resourceid,language));";
                        cmdResource.ExecuteNonQuery();
                        cmdResourceComment.CommandText = "CREATE TABLE resourcecomments (resourceid int(10) PRIMARY KEY NOT NULL, comment text);";
                        cmdResourceComment.ExecuteNonQuery();

                        cmdResource.CommandText = "INSERT INTO resources (resourceid, language, translation, translatorcomment) VALUES (@resourceid, @language, @translation, @translatorcomment);";
                        cmdResource.Parameters.AddWithValue("@resourceid", "");
                        cmdResource.Parameters.AddWithValue("@language", "");
                        cmdResource.Parameters.AddWithValue("@translation", "");
                        cmdResource.Parameters.AddWithValue("@translatorcomment", "");

                        cmdResourceComment.CommandText = "INSERT INTO resourcecomments (resourceid, comment) VALUES (@resourceId, @comment);";
                        cmdResourceComment.Parameters.AddWithValue("@resourceid", "");
                        cmdResourceComment.Parameters.AddWithValue("@comment", "");

                        foreach(KeyValuePair<int, RBResource> resource in resources) {
                            try {
                                cmdResource.Parameters["@resourceid"].Value = resource.Value.ID;
                                foreach(KeyValuePair<string, RBTranslation> translation in resource.Value.Translations) {
                                    cmdResource.Parameters["@language"].Value = translation.Value.LanguageCode;
                                    cmdResource.Parameters["@translation"].Value = translation.Value.Translation;
                                    cmdResource.Parameters["@translatorcomment"].Value = translation.Value.Comment;
                                    cmdResource.ExecuteNonQuery();
                                }
                                if(resource.Value.Comment.Length > 0) {
                                    cmdResourceComment.Parameters["@resourceid"].Value = resource.Value.ID;
                                    cmdResourceComment.Parameters["@comment"].Value = resource.Value.Comment;
                                    cmdResourceComment.ExecuteNonQuery();
                                }
                                numExported++;
                                log(new StatusMessage(string.Format("Exported resource #{0}", resource.Value.ID), StatusMessage.StatusMessageType.Success));
                            } catch(Exception ex) {
                                log(new StatusMessage(string.Format("Error saving resource: {0}", ex.Message), StatusMessage.StatusMessageType.Error));
                            }
                        }
                    }
                    trans.Commit();
                }
                log(new StatusMessage(string.Format("{0} resources exported.", numExported), StatusMessage.StatusMessageType.Status));
            } catch(Exception ex) {
                error = ex;
            } finally {
                if(db != null && db.Connected) {
                    db.CloseConnection();
                }
            }
            if(error != null) {
                log(new StatusMessage(string.Format("Error: {0}", error.Message), StatusMessage.StatusMessageType.Error));
                throw error; // after closing the DB connection
            }
        }

        /// <summary>
        /// Saves a ZipOutputStream, preserving it's structure.
        /// </summary>
        /// <param name="stream">The underlying MemoryStream.</param>
        /// <param name="outputFile">The output file (or directory).</param>
        /// <param name="extractZip"><c>true</c> if the zip should be extracted, otherwise <c>false</c>.</param>
        public static void SaveZip(MemoryStream stream, FileInfo outputFile, bool extractZip) {
            if(Directory.Exists(outputFile.FullName)) { // it's a directory
                ExtractZip(stream, outputFile.FullName);
            } else {
                FileStream fs;
                if(extractZip) { // extract the zip to the directory specified
                    ExtractZip(stream, outputFile.Directory.FullName);
                } else { // just write the zip file
                    fs = outputFile.OpenWrite();
                    fs.Write(stream.GetBuffer(), 0, (int)stream.Position);
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// Extracts a ZipOutputStream.
        /// </summary>
        /// <param name="inputStream">The underlying MemoryStream.</param>
        /// <param name="outputPath">The extraction path.</param>
        public static void ExtractZip(MemoryStream inputStream, string outputPath) {
            if(!Directory.Exists(outputPath)) {
                Directory.CreateDirectory(outputPath);
            }
            inputStream.Seek(0, SeekOrigin.Begin);
            using(ZipInputStream stream = new ZipInputStream(inputStream)) {
                ZipEntry entry;
                while((entry = stream.GetNextEntry()) != null) {
                    string directoryName = Path.Combine(outputPath, Path.GetDirectoryName(entry.Name));
                    string fileName = Path.GetFileName(entry.Name);
                    if(directoryName.Length > 0 && !Directory.Exists(directoryName)) { // create the directory if it doesn't exist
                        Directory.CreateDirectory(directoryName);
                    }
                    if(fileName.Length > 0) {
                        using(FileStream streamWriter = File.Create(Path.Combine(directoryName, fileName))) {
                            int size = 2048;
                            byte[] data = new byte[2048];
                            while(true) {
                                size = stream.Read(data, 0, data.Length);
                                if(size > 0) {
                                    streamWriter.Write(data, 0, size);
                                } else {
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }

        private class TranslatedElement {
            public string ElementName { get; set; }
            public string Translation { get; set; }
            public string TranslatorComment { get; set; }

            public TranslatedElement() {
                this.ElementName = string.Empty;
                this.Translation = string.Empty;
                this.TranslatorComment = string.Empty;
            }
            public TranslatedElement(string elementName, string translation, string translatorComment) {
                this.ElementName = elementName;
                this.Translation = translation;
                this.TranslatorComment = translatorComment;
            }
        }
    }
}